package com.thh.datamonitor.executor.dao;

import com.thh.common.exception.ServiceException;
import com.thh.common.exception.sql.SqlException;
import com.thh.datamonitor.executor.AbstractDbExecutor;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @Desc 通用的dao
 * @Author tanhh
 * @Date 2022/7/8 14:08
 * @Version 1.0
 **/
public class CommonDao {

    private final static Logger logger = LoggerFactory.getLogger(CommonDao.class);

    public void close(Connection connection, Statement st , ResultSet rs) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                throw new SqlException("关闭连接失败！");
            }
        }
        try {
            if (rs != null) {
                rs.close();
            }
            if (st != null) {
                st.close();
            }
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }
    }

    public void close(Connection conn) {
        close(conn, null, null);
    }

    public void close(Statement stmt , ResultSet rs) {
        close(null, stmt , rs);
    }

    public void close(Statement stmt) {
        close(null, stmt , null);
    }



    // ============================ 执行sql ===================================================


    /*
     * @Author tanhuihuang
     * @Description  执行sql
     * @Date 14:21 2022/7/8
     * @param connection
     * @param sql
     * @param dataChangeMaxCount  最大变更数
     * @return long 变更条数
     **/
    public long executeInsertUpdateDeleteSql(Connection connection, String sql, int dataChangeMaxCount) throws Exception {
        Statement stmt = null;
        try {
            //不要自动提交
            connection.setAutoCommit(false);
            stmt = connection.createStatement();
            int count = stmt.executeUpdate(sql);
            if (count > dataChangeMaxCount) {
                connection.rollback();
                throw new SqlException("影响超过" + dataChangeMaxCount + "条数据无法执行，请提交数据变更申请");
            } else {
                connection.commit();
            }
            return count;
        } catch (Exception e) {
            connection.rollback();
            logger.error(e.getMessage(), e);
            throw new SqlException(e);
        } finally {
            close(stmt, null);
            //结束后自动提交
            connection.setAutoCommit(true);
        }
    }


    /**
     * 执行不提交的插入，需要在外面提交
     *
     * @param connection
     * @param sql
     * @throws Exception
     */
    public void executeInsertSqlNoCommit(Connection connection, String sql) throws Exception {
        Statement stmt = null;
        try {
            //不要自动提交
            connection.setAutoCommit(false);
            stmt = connection.createStatement();
            stmt.executeUpdate(sql);
        } catch (SQLException e) {
            connection.rollback();
            logger.error("sql执行失败！error " + e.getMessage(), e);
            throw new ServiceException("sql执行失败！error " + e.getMessage());
        } finally {
            close(stmt, null);
        }
    }



    protected List<Map<String, Object>> queryForList(Connection connect, String finalSql) throws SQLException {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ResultSetMetaData rsmd = null;
        int maxSize = -1;
        String[] fields = null;
        List<String> times = new ArrayList<>();
        List<String> clob = new ArrayList<>();
        List<String> binary = new ArrayList<>();
        List<String> longs = new ArrayList<>();
        List<Map<String, Object>> rows = new ArrayList<>();
        try {
            pstmt = connect.prepareStatement(finalSql);
            rs = pstmt.executeQuery();
            rsmd = rs.getMetaData();
            maxSize = rsmd.getColumnCount();
            fields = new String[maxSize];
            for (int i = 0; i < maxSize; i++) {
                fields[i] = rsmd.getColumnLabel(i + 1);
                if (("java.sql.Timestamp".equals(rsmd.getColumnClassName(i + 1))) || ("oracle.sql.TIMESTAMP".equals(rsmd.getColumnClassName(i + 1)))) {
                    times.add(fields[i]);
                }
                if (("oracle.jdbc.OracleClob".equals(rsmd.getColumnClassName(i + 1))) || ("oracle.jdbc.OracleBlob".equals(rsmd.getColumnClassName(i + 1)))) {
                    clob.add(fields[i]);
                }
                if ("[B".equals(rsmd.getColumnClassName(i + 1))) {
                    binary.add(fields[i]);
                }
                if ("java.lang.Long".equals(rsmd.getColumnClassName(i + 1))) {
                    longs.add(fields[i]);
                }
            }

            Map<String, Object> row = null;
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            while (rs.next()) {
                row = new LinkedHashMap<>();
                for (int i = 0; i < maxSize; i++) {
                    Object value = times.contains(fields[i]) ? rs.getTimestamp(fields[i]) : rs.getObject(fields[i]);
                    if ((times.contains(fields[i])) && (value != null)) {
                        value = sdf.format(value);
                    }
                    if ((clob.contains(fields[i])) && (value != null)) {
                        value = "(Blob)";
                    }
                    if ((binary.contains(fields[i])) && (value != null)) {
                        value = new String((byte[]) value);
                    }
                    if ((longs.contains(fields[i])) && (value != null)) {
                        value = value + "";
                    }
                    row.put(fields[i], value);
                }
                rows.add(row);
            }
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
            throw e;
        } finally {
            close(pstmt, rs);
        }
        return rows;
    }


    public List<Map<String, Object>> executeSqlForColumns(Connection conn, String sql) {
        String finalSql = " select * from  ( " + sql + " ) tab  limit 1 ";
        if ((sql.indexOf("show") == 0) || (sql.indexOf("SHOW") == 0)) {
            finalSql = sql;
        }

        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ResultSetMetaData rsmd = null;
        int maxSize = -1;
        List<Map<String, Object>> rows = new ArrayList<>();
        try {
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            rsmd = rs.getMetaData();
            maxSize = rsmd.getColumnCount();
            for (int i = 0; i < maxSize; i++) {
                Map<String, Object> map = new HashMap<>();
                map.put("column_name", rsmd.getColumnLabel(i + 1));
                map.put("data_type", rsmd.getColumnTypeName(i + 1));
                map.put("data_precision", rsmd.getPrecision(i + 1));
                map.put("data_scale", rsmd.getScale(i + 1));
                rows.add(map);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(  pstmt ,  rs);
        }
        return rows;
    }



    protected long executeQueryForCount(Connection connect, String sql) {
        String finalSql = "select count(1) from (" + sql + ")";
        long rowCount = 0;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = connect.createStatement();
            rs = stmt.executeQuery(finalSql);
            rs.next();
            rowCount = rs.getLong(1);
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        } finally {
            close(stmt, rs);
        }
        return rowCount;
    }


    protected long executeQueryForCount2(Connection connect, String sql) {
        long rowCount = 0;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = connect.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            rs = stmt.executeQuery(sql);
            rs.last();
            rowCount = rs.getRow();
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        } finally {
            close(stmt, rs);
        }
        return rowCount;
    }

    public void executeInsertUpdateDeleteSqlAfterCheck(Connection connection, List<String> sqlList, int applyNum) throws Exception {
        int count = 0;
        try {
            //不要自动提交
            connection.setAutoCommit(false);
            Statement stmt = null;
            for (String sql : sqlList) {
                try {
                    stmt = connection.createStatement();
                    int countOne = stmt.executeUpdate(sql);
                    count += countOne;
                } catch (SQLException e) {
                    connection.rollback();
                    throw e;
                } finally {
                    close(stmt, null);
                }
            }

            if (count == applyNum) {
                connection.commit();
            } else {
                connection.rollback();
                throw new SqlException("实际执行SQL行数" + count + "和填写的影响行数" + applyNum + "不一致，执行失败");
            }
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            throw new SqlException(e.getMessage());
        } finally {
            //结束后把连接设置回自动提交
            connection.setAutoCommit(true);
        }
    }




}
